﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using NPOI.HSSF.Util;
using System.IO;
using NPOI.SS.Util;
using System.Runtime.Serialization.Formatters.Binary;
using System.Security.Cryptography;

namespace Helpers
{
    public class Helper_Excel
    {
        private IWorkbook wb;
        private ISheet sheet;
        private string fileName = string.Empty;

        #region 导出数据方法
        ICellStyle style1;
        ICellStyle style2;
        ICellStyle style3;
        public void GetFont1(IWorkbook workbook)
        {
            style1 = workbook.CreateCellStyle();
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            style1.VerticalAlignment = VerticalAlignment.CENTER;
            style1.BorderBottom = BorderStyle.THIN;
            style1.BorderLeft = BorderStyle.THIN;
            style1.BorderRight = BorderStyle.THIN;
            style1.BorderTop = BorderStyle.THIN;
            IFont font = workbook.CreateFont();
            font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
            style1.SetFont(font);
        }
        public void GetFont2(IWorkbook workbook)
        {
            style2 = workbook.CreateCellStyle();
            style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            style2.VerticalAlignment = VerticalAlignment.CENTER;
            style2.BorderBottom = BorderStyle.THIN;
            style2.BorderLeft = BorderStyle.THIN;
            style2.BorderRight = BorderStyle.THIN;
            style2.BorderTop = BorderStyle.THIN;
        }
        /// <summary>
        /// 导出Excel初始化
        /// </summary>
        public Helper_Excel()
        {
            wb = new HSSFWorkbook();
            GetFont1(wb);
            GetFont2(wb);
        }

        public void GetAllBord()
        {
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                for (int j = 0; j < 23; j++)
                {
                    getCells(i, j).CellStyle = style2;
                }
            }
        }

        public void getNewSheet(string SheetName)
        {
            sheet = wb.CreateSheet(SheetName);
        }

        public void MergeCell(int cellsRowIndex, int EndcellsRowIndex, int cellsColumIndex, int EndcellsColumIndex)
        {
            sheet.AddMergedRegion(new CellRangeAddress(cellsRowIndex, EndcellsRowIndex, cellsColumIndex, EndcellsColumIndex));
        }

        //正常
        public void getCells(int cellsRowIndex, int cellsColumIndex, string str)
        {
            ICell cell;
            if (sheet.GetRow(cellsRowIndex) == null)
            {
                cell = sheet.CreateRow(cellsRowIndex).CreateCell(cellsColumIndex);
            }
            else
            {
                if (sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex) == null)
                    cell = sheet.GetRow(cellsRowIndex).CreateCell(cellsColumIndex);
                else
                    cell = sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex);
            }
            cell.SetCellValue(str);
            cell.CellStyle = style2;
        }

        //车站
        public void getCellsStyle1(int cellsRowIndex, int cellsColumIndex, string str)
        {
            ICell cell;
            if (sheet.GetRow(cellsRowIndex) == null)
            {
                cell = sheet.CreateRow(cellsRowIndex).CreateCell(cellsColumIndex);
            }
            else
            {
                if (sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex) == null)
                    cell = sheet.GetRow(cellsRowIndex).CreateCell(cellsColumIndex);
                else
                    cell = sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex);
            }
            cell.SetCellValue(str);
            cell.CellStyle = style1;
        }

        public ICell getCells(int cellsRowIndex, int cellsColumIndex)
        {
            ICell cell;
            if (sheet.GetRow(cellsRowIndex) == null)
            {
                cell = sheet.CreateRow(cellsRowIndex).CreateCell(cellsColumIndex);
            }
            else
            {
                if (sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex) == null)
                    cell = sheet.GetRow(cellsRowIndex).CreateCell(cellsColumIndex);
                else
                    cell = sheet.GetRow(cellsRowIndex).GetCell(cellsColumIndex);
            }
            return cell;
        }

        public void SaveFile(string filePath)
        {
            string fileName = filePath;
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            FileStream sw = File.Create(fileName);

            wb.Write(sw);

            sw.Close();
            GC.Collect();
        }

        public int SetCellColumnWidth(int y1)
        {
            return sheet.GetColumnWidth(y1);
        }

        /// <summary>
        /// 获取格子里的内容
        /// </summary>
        /// <param name="i">行数</param>
        /// <param name="j">列数</param>
        /// <returns>字符串</returns>
        public string GetCellValue(int i, int j)
        {
            return sheet.GetRow(i).GetCell(j).StringCellValue;
        }

        public void SetCellColumnWidth(int y1, int y2, int width)
        {
            for (int i = y1; i <= y2; i++)
            {
                sheet.SetColumnWidth(i, width);
            }
        }

        public void SetCellRowHeight(int x1, int x2, int height)
        {
            for (int i = x1; i <= x2; i++)
            {
                if (sheet.GetRow(i) == null)
                {
                    sheet.CreateRow(i).HeightInPoints = height;
                }
                else
                {
                    sheet.GetRow(i).HeightInPoints = height;
                }
            }
        }
        #endregion
    }

    [Serializable]
    public class CllStyle
    {
        public string FontName { get; set; }
        public short FontBoldweight { get; set; }
        public short Color { get; set; }
        public short FontHeightInPoints { get; set; }
        public VerticalAlignment VerticalAlignment { get; set; }
        public HorizontalAlignment Alignment { get; set; }
        public bool WrapText { get; set; }
        public short FillBackgroundColor { get; set; }
        public FillPatternType FillPattern { get; set; }
        public short FillForegroundColor { get; set; }
    }
}
